package com.xinziruo.richclient.support.persist;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class MysqlDBService {
	/**
	 * 取得一个数据库连接
	 * @return
	 * @throws SQLException
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws ClassNotFoundException
	 * 
	 */
	public Connection getConnection() throws SQLException,InstantiationException
	 ,IllegalAccessException,ClassNotFoundException{
		Connection conn = null;
		//加载数据库驱动类
		Class.forName("com.mysql.jdbc.Driver").newInstance();
		//数据连接路径
		String url = "jdbc:mysql://localhost:3306/mobile?useUnicode=true&characterEncoding=UTF-8";
		//数据库用户名、密码
		String user = "root";
		String password = "";
		
		//取得一个数据库连接
		conn = DriverManager.getConnection(url, user, password);
		return conn;
		
	}
	
	
	/**
	 * 根据传入的SQL语句返回一个对象的链表
	 * @param good
	 * @return 
	 * @throws Exception
	 */
	public void insert(ProductSource productSource) throws Exception{
		Connection conn = null;
		PreparedStatement ps = null;
		//根据product对象的属性生成SQL语句
		String sql = "insert into product_source(id,source,title,image_url,price,link,create_time,oldprice) values('"+
				productSource.getId()+"','"+
				productSource.getSource()+"','"+
				productSource.getTitle()+"','"+
				productSource.getImageUrl()+"',"+
				productSource.getPrice()+",'"+
				productSource.getLink()+"',"+
				productSource.getCreateTime()+","+
				productSource.getOldprice()+")";
		try{
			conn = getConnection();
			ps = conn.prepareStatement(sql); 
			ps.executeUpdate();		
		}catch(SQLException sqle){
			throw new SQLException("select data exception:"+sqle.getMessage());
		}catch(Exception e){
			throw new Exception("System e Exception:"+e.getMessage());
		}finally{
			try{
				if(null != ps){
					ps.close();
				}
			}catch(Exception e){
				throw new Exception("System e Exception:"+e.getMessage());
			}
			try{
				if(null != conn){
					conn.close();
				}
			}catch(Exception e){
				throw new Exception("System e Exception:"+e.getMessage());
			}
		}	
	}
	
	/**
	 * 根据传入的对象更新数据库记录(标识link)
	 * @param sql
	 * @throws Exception
	 */
	public void update(ProductSource productSource) throws Exception{
		Connection conn = null;
		PreparedStatement ps = null;
		//根据product对象的属性生成SQL语句
		String sql = "update product_source  set source='"+
				productSource.getSource()+"',price="+
				productSource.getPrice()+",title='"+
				productSource.getTitle()+"',oldprice="+
				productSource.getOldprice()+",link='"+
				productSource.getLink()+"',update_time="+
				productSource.getUpdateTime()+" where id='"+productSource.getId()+"'";		
		
		try{
			conn = getConnection();
			ps = conn.prepareStatement(sql); //
			ps.executeUpdate();		
		}catch(SQLException sqle){
			throw new SQLException("select data exception:"+sqle.getMessage());
		}catch(Exception e){
			throw new Exception("System e Exception:"+e.getMessage());
		}finally{
			try{
				if(null != ps){
					ps.close();
				}
			}catch(Exception e){
				throw new Exception("System e Exception:"+e.getMessage());
			}
			try{
				if(null != conn){
					conn.close();
				}
			}catch(Exception e){
				throw new Exception("System e Exception:"+e.getMessage());
			}
		}	
	}
	
	/**
	 * 根据传入的对象删除数据库中对应的这条记录
	 * @param good
	 * @throws Exception
	 */
	public void delete(ProductSource  productSource) throws Exception{
		Connection conn = null;
		PreparedStatement ps = null;
		//根据product对象的属性生成SQL语句
		String sql = "delete from product_source where id='"+productSource.getId()+"'";	
		try{
			conn = getConnection();
			ps = conn.prepareStatement(sql); 
			ps.executeUpdate();		
		}catch(SQLException sqle){
			throw new SQLException("select data exception:"+sqle.getMessage());
		}catch(Exception e){
			throw new Exception("System e Exception:"+e.getMessage());
		}finally{
			try{
				if(null != ps){
					ps.close();
				}
			}catch(Exception e){
				throw new Exception("System e Exception:"+e.getMessage());
			}
			try{
				if(null != conn){
					conn.close();
				}
			}catch(Exception e){
				throw new Exception("System e Exception:"+e.getMessage());
			}
		}	
	}
	
	
	/**
	 * 根据商品编号提取商品数据
	 * @param sql
	 * @return
	 * @throws Exception
	 */
	public ProductSource queryById(String id) throws Exception{
		ProductSource productSource = new ProductSource();
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			conn = getConnection();
			stmt = conn.createStatement();
			String sql = "select * from product_source where id='"+id+"'";	
			rs = stmt.executeQuery(sql);
			if(!rs.next()){
				return null;
			}
			while(rs.next()){//delete from good where id="+goodid
				//查询的结果转化为good对象
				productSource.setId(rs.getString("id"));
				productSource.setCreateTime(rs.getInt("create_time"));
				productSource.setImageUrl(rs.getString("image_url"));
				productSource.setLink(rs.getString("link"));
				productSource.setOldprice(rs.getDouble("oldprice"));
				productSource.setPrice(rs.getDouble("price"));
				productSource.setSource(rs.getString("source"));
				productSource.setTitle(rs.getString("title"));
				productSource.setUpdateTime(rs.getInt("update_time"));
			}			
		}catch(SQLException sqle){
			throw new SQLException("select data exception:"+sqle.getMessage());
		}catch(Exception e){
			throw new Exception("System e Exception:"+e.getMessage());
		}finally{
			try{
				if(null != rs){
					rs.close();
				}
			}catch(Exception e){
				throw new Exception("System e Exception:"+e.getMessage());
			}
			try{
				if(null != stmt){
					stmt.close();
				}
			}catch(Exception e){
				throw new Exception("System e Exception:"+e.getMessage());
			}
			try{
				if(null != conn){
					conn.close();
				}
			}catch(Exception e){
				throw new Exception("System e Exception:"+e.getMessage());
			}
		}
		return productSource;		
	}	
	
}
